package team.chuangshu.lm.EXCELServiceTest;

import org.apache.poi.hssf.usermodel.*;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import team.chuangshu.lm.entity.Message;
import team.chuangshu.lm.service.MessageService;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.List;


@SpringBootTest
@RunWith(SpringRunner.class)
public class EXCELService {
    @Autowired
    private MessageService messageService;

    @Test
    public void test1() throws Exception {
        List<Message> messageList = messageService.findByParams(new Message());
        //HSSFWorkbook wb1 = new HSSFWorkbook(new FileInputStream(new File(System.getProperty("user.dir")+ "/src/main/resources/" +"fileTempalte/tempalte.xls")));
        HSSFWorkbook wb1 = new HSSFWorkbook(new FileInputStream(new File("F:\\code\\java\\lm\\src\\main\\resources\\fileTemplate\\tempalte.xls")));
        //获取模板表
        HSSFSheet sheet = wb1.getSheetAt(0);

        //定义一个样式（模板中第一行第一列的样式）
        HSSFCellStyle cellStyle = sheet.getRow(0)
                .getCell(0)
                .getCellStyle();

        //获取模板列宽
        float width = sheet.getColumnWidthInPixels(sheet.getRow(0).getCell(0).getColumnIndex());

        //获取模板行高
        float height = sheet.getRow(0).getHeightInPoints();

        for (int i = 0; i < messageList.size(); i++) {
            HSSFRow row = wb1.getSheetAt(0).createRow(i + 1);
            Message message = messageList.get(i);
            //设置行高
            row.setHeightInPoints(height);
            //假条编号
            HSSFCell idCell = row.createCell(0);
            idCell.setCellValue(message.getMessageId());
            //请假学生
            HSSFCell nameCell = row.createCell(1);
            nameCell.setCellValue(message.getStudentName());
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
            //假条开始时间
            HSSFCell beginTiCell = row.createCell(2);
            beginTiCell.setCellValue(sdf.format(message.getBeginTime()));
            //假条结束时间
            HSSFCell endTiCell = row.createCell(3);
            endTiCell.setCellValue(sdf.format(message.getEndTime()));
            //假条状态
            HSSFCell statusCell = row.createCell(4);
            String status = "";
            switch (message.getStatus()){
                case "0": status = "申请中"; break;
                case "1": status = "已批准"; break;
                case "2": status = "已拒绝"; break;
                case "3": status = "代销价"; break;
                case "4": status = "已申请销假"; break;
                case "5": status = "已销假"; break;
            }
            statusCell.setCellValue(status);
            //审批时间
            HSSFCell commintaTi = row.createCell(5);
            String conmmitTi ;
            if (message.getIsReportback() == 0) {
                conmmitTi = "";
            }else {
                if (message.getIsLongtime() == 0) {
                    conmmitTi = message.getLevelOneTime().toString();
                }else {
                    conmmitTi = message.getLevelTwoTime().toString();
                }
            }
            commintaTi.setCellValue(conmmitTi);
            //需要销假标识
            HSSFCell reportSign = row.createCell(6);
            if (message.getIsReportback() == 0) {
                reportSign.setCellValue("否");
            }else {
                reportSign.setCellValue("是");
            }
        }
        FileOutputStream stream = new FileOutputStream(new File(System.getProperty("user.dir")+ "/src/main/resources/" +"fileTemplate/test.xls"));
        wb1.write(stream);
        stream.close();



    }
    @Test
    public void test2() throws Exception {
        //第一步创建workbook
        HSSFWorkbook wb = new HSSFWorkbook();

        //第二步创建sheet
        HSSFSheet sheet = wb.createSheet("测试");

        //第三步创建行row:添加表头0行
        HSSFRow row = sheet.createRow(0);
        HSSFCellStyle  style = wb.createCellStyle();
        //style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  //居中


        //第四步创建单元格
        HSSFCell cell = row.createCell(0); //第一个单元格
        cell.setCellValue("姓名");
        cell.setCellStyle(style);



        cell = row.createCell(1);         //第二个单元格
        cell.setCellValue("年龄");
        cell.setCellStyle(style);


        //第五步插入数据

        for (int i = 0; i < 5; i++) {
            //创建行
            row = sheet.createRow(i+1);
            //创建单元格并且添加数据
            row.createCell(0).setCellValue("aa"+i);
            row.createCell(1).setCellValue(i);

        }

        //第六步将生成excel文件保存到指定路径下
        try {
            FileOutputStream fout = new FileOutputStream("C:\\Users\\zhang\\Desktop\\test.xls");
            wb.write(fout);
            fout.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
